﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using DataControl;
using System.Data;
using System.Text;

namespace ATW.MT.BUS
{
    public class bus_T_Contract
    {
        DataAccess da = null;
        public bus_T_Contract()
        {
            da = new DataAccess();
        }
        #region 方法
        /// <summary>
        /// 合同分页查询
        /// </summary>
        /// <param name="name">归属人</param>
        /// <param name="type">类型，1客户，2供应商</param>
        /// <param name="status">(0默认 客户：1.已确认，并生成分供商合同  分供商：1.已确认 2删除    3.已确认，但未生成分供商合同)</param>
        /// <param name="timeFrom">起始时间</param>
        /// <param name="timeTo">结束时间</param>
        /// <param name="page"></param>
        /// <param name="pagesize"></param>
        /// <returns></returns>
        public DataTable ContractPagerQuery(string name,string title, string type, string status, string timeFrom, string timeTo,
            int page, int pagesize)
        {
            StringBuilder sql = new StringBuilder();
            sql.AppendFormat(@"select TC.ContractID,
	                            case when TC.[type]=1 then Ureq.tauName
		                             when TC.[type]=2 then Ures.tauName
		                             end name,
                                req.title,
	                            TC.[type],
	                            TC.rowstatus,
                                TC.RequestID,
	                            convert(nvarchar(20),TC.create_time,111) create_time,
                                TC.create_time time,
                                case when TC.[type]=1 then req.RequestID
                                     when TC.[type]=2 then res.ResponseID end OID,
                                case when TC.[type]=1 then req.Rowstatus 
                                     when TC.[type]=2 then res.Rowstatus end Ostatus,
                                isnull(s1.exnum,0) exnum
                             from T_Contract TC
                            left join T_Request req on TC.RequestID=req.RequestID
                            left join T_AppUser Ureq on req.tauloginid=Ureq.tauLoginId
                            left join T_Response res on TC.ResponseID=res.ResponseID
                            left join T_AppUser Ures on res.tauloginid=Ures.tauLoginId
                            left join (select COUNT(*) exnum,ContractID from T_Exception group by ContractID)s1 on s1.ContractID=TC.ContractID
                            where 1=1 ");
            if (!string.IsNullOrEmpty(name))
            {
                sql.Append(@" and (case when TC.[type]=1 then Ureq.tauName
		                              when TC.[type]=2 then Ures.tauName
		                              end) like '%" + name + "%' ");
            }
            if (!string.IsNullOrEmpty(type))
            {
                sql.Append(" and TC.type='" + type + "'");
            }
            if (!string.IsNullOrEmpty(title))
            {
                sql.Append(" and req.title like '%" + title + "%'");
            }
            if (!string.IsNullOrEmpty(status))
            {
                sql.Append(" and TC.rowstatus='" + status + "'");
            }
            if (!string.IsNullOrEmpty(timeFrom))
            {
                sql.Append(" and TC.create_time>CAST('" + timeFrom + " 00:00:00' as datetime)");
            }
            if (!string.IsNullOrEmpty(timeTo))
            {
                sql.Append("and TC.create_time<CAST('" + timeTo + " 23:59:59' as datetime)");
            }
            return da.PageSize(sql.ToString(), page, pagesize, "ContractID","time desc");
        }
        /// <summary>
        /// 合同明细查询
        /// </summary>
        /// <param name="ContractID"></param>
        /// <returns></returns>
        public DataTable ContractSingleQuery(string ContractID)
        {
            StringBuilder sql = new StringBuilder();
            sql.AppendFormat(@"select * from T_contract where ContractID='{0}'", ContractID);
            return da.ExecuteDataTable(sql.ToString());
        }
        /// <summary>
        /// 修改合同状态
        /// </summary>
        /// <param name="ContractID"></param>
        /// <param name="status">0已生成1已确认2删除3待处理（未生成供应商合同）</param>
        /// <returns></returns>
        public int ContractChangeStatus(string ContractID, string status)
        {
            StringBuilder sql = new StringBuilder();
            sql.AppendFormat(@"update T_contract set rowstatus='" + status + "' where ContractID='" + ContractID + "'");
            return da.ExcuteNoQuery(sql.ToString());
        }
        /// <summary>
        /// 修改客户合同状态
        /// </summary>
        /// <param name="requestID"></param>
        /// <returns></returns>
        public int ContractPushed(string requestID)
        {
            StringBuilder sql = new StringBuilder();
            sql.AppendFormat(@"update T_contract set rowstatus='1' where requestID='{0}' and type='1'", requestID);
            return da.ExcuteNoQuery(sql.ToString());
        }
        /// <summary>
        /// 获取要生成的合同列表
        /// </summary>
        /// <param name="ContractID"></param>
        /// <returns></returns>
        public DataTable getPushList(string requestID)
        {
            StringBuilder sql = new StringBuilder();
            sql.AppendFormat(@"select ContractID,tc.ResponseID,res.tauloginid from T_contract TC
                                    left join T_Response res on TC.ResponseID=res.ResponseID
                                     where tc.RequestID='{0}' and TC.[type]='2'", requestID);
            return da.ExecuteDataTable(sql.ToString());
        }
        public int UpdateReqStatus(string requestID)
        {
            StringBuilder sql = new StringBuilder();
            sql.AppendFormat(@"update T_Request set rowstatus='4' where RequestID='{0}'",requestID);
            return da.ExcuteNoQuery(sql.ToString());
        }
        public int UpdateResStatus(string responseID)
        {
            StringBuilder sql = new StringBuilder();
            sql.AppendFormat(@"update T_Response set rowstatus='4' where ResponseID='{0}'",responseID);
            return da.ExcuteNoQuery(sql.ToString());
        }
        #endregion
    }
}